Sub 产量对标()

Dim RowMax As Integer
Dim I As Range      '当前单元格
Static j1 As Integer    '行号
Static N1 As Integer    '产量类别编号
If ActiveSheet.Range("A1").Text <> "*" Then
    MsgBox "请选择产量表,标志符（在表A1单元格中）为：*" '用单元格A1中字符来区分表
    Exit Sub
End If
'获取最大行号
RowMax = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
N1 = 0  '初始化N1
For m = 1 To RowMax Step 1
    Set I = Cells(m, 1)
    '根据单元格内容判断属于哪块产量（输入单元格类型，返回整型）
    If I.Text Like _
        "*炭素制品*" Then
        N1 = 5    '炭素产量
    ElseIf I.Text _
        Like "*发电量*" Then
        N1 = 6 '电厂产量（发电量）
    ElseIf I.Text _
        Like "*电解铝*" Then
        N1 = 7 '电解产量
    End If
    
    j1 = PDHH1(N1, I)   '根据产量类别判断第一列各单元格在目标表中的行号
    If j1 <> 0 Then     '按类别将值填入目标表行号中，不在范围内的行号为0
        Select Case N1
            Case 5
                Sheet5.Cells(j1, 4).Value = I.Offset(0, 5).Text
                Sheet5.Cells(j1, 6).Value = I.Offset(0, 6).Text
            Case 6
                Sheet6.Cells(j1, 4).Value = I.Offset(0, 5).Text
                Sheet6.Cells(j1, 6).Value = I.Offset(0, 6).Text
            Case 7
                Sheet7.Cells(j1, 4).Value = I.Offset(0, 5).Text
                Sheet7.Cells(j1, 6).Value = I.Offset(0, 6).Text
        End Select
    End If
Next
End Sub

'根据产量类别和单元格内容来判断出在目标表中的行号，不再统计范围内的公司将行号设为0
Function PDHH1(xlh As Integer, Ncells As Range) As Integer
Select Case xlh
    Case 5  '炭素产量
        With Ncells
            If .Text Like "*山东分公司*" Then
                PDHH1 = 4
                ElseIf .Text Like "*河南分公司*" Then
                PDHH1 = 5
                ElseIf .Text Like "*贵州分公司*" Then
                PDHH1 = 6
                ElseIf .Text Like "*广西分公司*" Then
                PDHH1 = 7
                ElseIf .Text Like "*青海分公司*" Then
                PDHH1 = 8
                ElseIf .Text Like "*山西华泽铝*" Then
                PDHH1 = 9
                ElseIf .Text Like "*兰州分公司*" Then
                PDHH1 = 10
                ElseIf .Text Like "*华圣铝业*" Then
                PDHH1 = 11
                ElseIf .Text Like "*抚顺铝业*" Then
                PDHH1 = 12
                ElseIf .Text Like "*焦作万方铝*" Then
                PDHH1 = 13
                ElseIf .Text Like "*山东华宇铝*" Then
                PDHH1 = 14
                ElseIf .Text Like "*华鹭铝业*" Then
                PDHH1 = 15
                ElseIf .Text Like "*包头铝业*" Then
                PDHH1 = 16
                ElseIf .Text Like "*连城分公司*" Then
                PDHH1 = 17
                ElseIf .Text Like "*青海黄河*" Then
                PDHH1 = 18
                Else: PDHH1 = 0
            End If
        End With
    Case 6  '电厂产量
        With Ncells
            If .Text Like "*山东分公司*" Then
                PDHH1 = 4
                ElseIf .Text Like "*河南分公司*" Then
                PDHH1 = 5
                ElseIf .Text Like "*贵州分公司*" Then
                PDHH1 = 6
                ElseIf .Text Like "*山西分公司*" Then
                PDHH1 = 7
                ElseIf .Text Like "*广西分公司*" Then
                PDHH1 = 8
                ElseIf .Text Like "*中州分公司*" Then
                PDHH1 = 9
                ElseIf .Text Like "*山西华泽铝*" Then
                PDHH1 = 10
                ElseIf .Text Like "*兰州分公司*" Then
                PDHH1 = 11
                ElseIf .Text Like "*焦作万方*" Then
                PDHH1 = 12
                ElseIf .Text Like "*山东华宇*" Then
                PDHH1 = 13
                ElseIf .Text Like "*重庆分公司*" Then
                PDHH1 = 14
                ElseIf .Text Like "*遵义*" Then
                PDHH1 = 15
                Else: PDHH1 = 0
            End If
        End With
    Case 7  '电解产量
        With Ncells
            If .Text Like "*山东分公司*" Then
                PDHH1 = 4
                ElseIf .Text Like "*贵州分公司*" Then
                PDHH1 = 5
                ElseIf .Text Like "*广西分公司*" Then
                PDHH1 = 6
                ElseIf .Text Like "*青海分公司*" Then
                PDHH1 = 7
                ElseIf .Text Like "*郑州研究院*" Then
                PDHH1 = 8
                ElseIf .Text Like "*山西华泽铝*" Then
                PDHH1 = 9
                ElseIf .Text Like "*兰州分公司*" Then
                PDHH1 = 10
                ElseIf .Text Like "*华圣铝业*" Then
                PDHH1 = 11
                ElseIf .Text Like "*抚顺铝业*" Then
                PDHH1 = 12
                ElseIf .Text Like "*焦作万方铝*" Then
                PDHH1 = 13
                ElseIf .Text Like "*遵义铝业*" Then
                PDHH1 = 14
                ElseIf .Text Like "*山东华宇铝*" Then
                PDHH1 = 15
                ElseIf .Text Like "*华鹭铝业*" Then
                PDHH1 = 16
                ElseIf .Text Like "*山西龙门铝*" Then
                PDHH1 = 17
                ElseIf .Text Like "*包头铝业*" Then
                PDHH1 = 18
                ElseIf .Text Like "*连城分公司*" Then
                PDHH1 = 19
                ElseIf .Text Like "*青海黄河水*" Then
                PDHH1 = 20
                Else: PDHH1 = 0
            End If
        End With
    Case Else
        PDHH1 = 0
End Select
End Function